package com.tetra.arena.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.bukkit.Bukkit;
import org.bukkit.ChatColor;
import org.bukkit.entity.Player;

import com.avaje.ebean.enhance.asm.Type;
import com.tetra.arena.core.ArenaSystems;
import com.tetra.arena.core.NotificationSender;
import com.tetra.arena.handlers.NotificationHandler;

public abstract class SQLMainC implements DataSource {

	protected Connection conn;
	ArenaSystems plugin;
	NotificationHandler NS;

	protected abstract void getConnection() throws ClassNotFoundException,
			SQLException;

	protected abstract void setup() throws SQLException;

	public SQLMainC(ArenaSystems instance) {
		this.plugin = instance;
		NS = new NotificationSender(instance);
	}

	public synchronized int getIntValue(String table, String field, String key,
			int intvalue, String strvalue) {
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			if (intvalue != -1) {
				rs = st.executeQuery("SELECT " + field + " FROM " + table
						+ " WHERE " + key + "=" + intvalue);
			} else if (strvalue != null) {
				rs = st.executeQuery("SELECT " + field + " FROM " + table
						+ " WHERE " + key + "='" + strvalue + "'");
			}
			if (rs != null) {
				while (rs.next()) {
					if (rs.getInt(field) != -1) {
						return rs.getInt(field);
					}
				}
			}
		} catch (SQLException ex) {

		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException ex) {
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			}
		}
		return -1;
	}

	private ResultSet getMembers(String table, String field, String key,
			int keyval) {
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			if (keyval != -1) {
				rs = st.executeQuery("SELECT " + field + " FROM " + table
						+ " WHERE " + key + "=" + keyval);
				if (rs != null) {
					return rs;
				}
			}
		} catch (SQLException ex) {

		} finally {

		}
		return null;
	}

	private int getDoubleCheckInt(String table, String field, String key,
			String key2, int key1val, int key2val) {
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			if (key1val != -1 && key2val != -1) {
				rs = st.executeQuery("SELECT " + field + " FROM " + table
						+ " WHERE " + key + "=" + key1val + " AND " + key2
						+ "=" + key2val);
				if (rs != null) {
					while (rs.next()) {
						if (rs.getInt(field) != -1) {
							return rs.getInt(field);
						}
					}
				}
			}
		} catch (SQLException ex) {

		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException ex) {
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			}
		}
		return 0;
	}

	// Team Manager begins//
	protected int getPlayerId(Player p) {
		return getIntValue("players", "player_id", "username", -1, p.getName());
	}

	protected int getPlayerTeamId(Player p) {
		return getIntValue("players", "team_id", "username", -1, p.getName());
	}

	protected int getTeamOpenState(int team_id) {
		return getIntValue("teams", "open", "id", team_id, null);
	}

	protected int getTeamOwner(int team_id) {
		return getIntValue("teams", "owner", "id", team_id, null);
	}

	protected int getTeamId(String TeamName) {
		return getIntValue("teams", "id", "team_name", -1, TeamName);
	}

	protected ResultSet getTeamMembers(int TeamId) {
		return getMembers("players", "username", "team_id", TeamId);
	}

	public synchronized void createTeam(String TeamName, Player creator) {
		PreparedStatement pst = null;
		int team_id = getPlayerTeamId(creator);
		int creator_id = getPlayerId(creator);
		if (team_id == 0) {
			try {
				pst = conn
						.prepareStatement("INSERT INTO teams (team_name,rating,win_count,loss_count,owner) VALUES(?,?,?,?,?);");
				pst.setString(1, TeamName);
				pst.setInt(2, 1500);
				pst.setInt(3, 0);
				pst.setInt(4, 0);
				pst.setInt(5, creator_id);
				creator.sendMessage(pst.toString());
				pst.executeUpdate();
				creator.sendMessage("You created the team: " + TeamName);
				joinTeam(creator, TeamName);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				if (e.getErrorCode() != 1062) { // Duplicate nick
					e.printStackTrace();
				} else {
					creator.sendMessage(ChatColor.RED
							+ "A team is already created under this name");
				}
			} finally {
				if (pst != null) {
					try {
						pst.close();
					} catch (SQLException sqlEx) {
					}
				}
			}
		} else {
			creator.sendMessage(ChatColor.RED
					+ "You cannot create a team while on a team.");
		}
	}

	public synchronized void deleteTeam(Player p) {
		PreparedStatement pst = null;
		int owner_id = getPlayerId(p);
		String team_name = getTeamName(getPlayerTeamId(p));
		try {
			pst = conn.prepareStatement("DELETE FROM teams WHERE owner=?");
			pst.setInt(1, owner_id);
			pst.executeUpdate();
			pst = conn
					.prepareStatement("UPDATE players SET team_id=? WHERE player_id=?;");
			pst.setNull(1, Type.INT);
			pst.setInt(2, owner_id);
			pst.executeUpdate();
			p.sendMessage(ChatColor.GREEN + "Team: " + team_name
					+ " has been disbanded.");
		} catch (SQLException e) {
			p.sendMessage(ChatColor.RED + "There is no team to delete.");
			e.printStackTrace();
		} finally {
			try {
				pst.close();
			} catch (SQLException e) {
			}
		}
	}

	public synchronized void sendInvite(Player sender, String target) {
		PreparedStatement pst = null;
		Player target_player = plugin.getServer().getPlayerExact(target);
		int team_id = getPlayerTeamId(sender);
		int sender_id = getPlayerId(sender);
		int player_id = getPlayerId(target_player);
		int owner_id = getTeamOwner(team_id);
		String team_name = getTeamName(team_id);
		if (target_player != null) {
			if (sender_id == owner_id) {
				try {
					pst = conn
							.prepareStatement("INSERT INTO invites (player_id,team_id) VALUES(?,?)");
					pst.setInt(1, player_id);
					pst.setInt(2, team_id);
					pst.executeUpdate();
					sender.sendMessage(ChatColor.GREEN
							+ plugin.getServer().getPlayer(target).getName()
							+ " has been invited.");
					target_player.sendMessage(ChatColor.GREEN
							+ "You have been invited to join " + team_name
							+ ". Type /arena team join <teamname> to join.");
				} catch (SQLException ex) {
					if (ex.getErrorCode() != 1062) {
						System.out.println(ex.getMessage());
					}
				} finally {
					try {
						pst.close();
					} catch (SQLException e) {
					}
				}
			} else {
				sender.sendMessage(ChatColor.RED
						+ "You are not allowed to invite players.");
			}
		} else {
			sender.sendMessage(ChatColor.RED
					+ "Unable to find user. Is he online?");
		}
	}

	private void deleteInvite(Player joined) {
		PreparedStatement pst = null;
		int player_id = getPlayerId(joined);
		try {
			pst = conn
					.prepareStatement("DELETE FROM invites WHERE player_id=?");
			pst.setInt(1, player_id);
			pst.executeUpdate();
		} catch (SQLException e) {
		} finally {
			try {
				pst.close();
			} catch (SQLException e) {
			}
		}
	}

	public synchronized void wipeInvites() {
		PreparedStatement pst = null;
		try {
			pst = conn.prepareStatement("DELETE FROM invites");
			pst.executeUpdate();
		} catch (SQLException e) {
		} finally {
			try {
				pst.close();
			} catch (SQLException e) {
			}
		}
	}

	private int checkInvited(Player p, String TeamName) {
		Statement st = null;
		ResultSet rs = null;
		int player_id = getPlayerId(p);
		int team_id = getTeamId(TeamName);
		try {
			st = conn.createStatement();
			rs = st.executeQuery("SELECT invite_id FROM invites WHERE player_id='"
					+ player_id + "'" + " AND team_id=" + team_id);
			if (rs != null) {
				while (rs.next()) {
					return rs.getInt("invite_id");
				}
			}
		} catch (SQLException ex) {

		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException ex) {
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			}
		}
		return 0;
	}

	public synchronized void AddPlayer(Player p) {
		PreparedStatement pst = null;
		try {
			pst = conn
					.prepareStatement("INSERT INTO players (username) VALUES(?);");
			pst.setString(1, p.getName());
			pst.executeUpdate();
		} catch (SQLException sqle) {
			if (sqle.getErrorCode() != 1062) {
				System.out.println(sqle.getMessage());
			}
		} finally {
			if (pst != null) {
				try {
					pst.close();
				} catch (SQLException ex) {

				}
			}
		}
	}

	private String getTeamName(int team_id) {
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery("SELECT team_name FROM teams WHERE id='"
					+ team_id + "'");
			while (rs.next()) {
				if (rs.getString("team_name") != null) {
					return rs.getString("team_name");
				}
			}
		} catch (SQLException ex) {

		} finally {
			if (st != null) {
				try {
					st.close();
				} catch (SQLException ex) {
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			}
		}
		return null;
	}

	public synchronized void joinTeam(Player p, String TeamName) {
		int player_id = getPlayerId(p);
		int player_team_id = getPlayerTeamId(p);
		int team_id = getTeamId(TeamName);
		int open_state = getTeamOpenState(team_id);
		int team_owner = getTeamOwner(team_id);
		PreparedStatement pst = null;
		if (player_team_id == 0) {
			if (team_id != 0) {
				if (open_state == 1 || player_id == team_owner
						|| checkInvited(p, TeamName) != 0) {
					try {
						NS.sendPlayerUpdate(p.getName()
								+ " has joined the arena team.",
								getTeamMembers(team_id), ChatColor.GREEN);
						pst = conn
								.prepareStatement("UPDATE players SET team_id=? WHERE player_id=?");

						pst.setInt(1, team_id);
						pst.setInt(2, player_id);
						pst.executeUpdate();
						p.sendMessage(ChatColor.GREEN
								+ "You have successfully joined: " + TeamName);
						if (checkInvited(p, TeamName) != 0) {
							deleteInvite(p);
						}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} finally {
						if (pst != null) {
							try {
								pst.close();
							} catch (SQLException ex) {

							}
						}
					}
				} else {
					p.sendMessage(ChatColor.RED
							+ "This team is not accepting players.");
				}
			} else {
				p.sendMessage(ChatColor.RED
						+ "Unable to find the specified team.");
			}
		} else {
			p.sendMessage(ChatColor.RED + "You are already on an arena team.");
		}
	}

	public synchronized void leaveTeam(Player p) {
		int player_id = getPlayerId(p);
		int player_team_id = getPlayerTeamId(p);
		int team_owner = getTeamOwner(player_team_id);
		String team_name = getTeamName(player_team_id);
		if (player_team_id != 0) {
			if (player_id != team_owner) {
				PreparedStatement pst = null;
				try {
					pst = conn
							.prepareStatement("UPDATE players SET team_id=? WHERE player_id=?;");
					pst.setNull(1, Type.INT);
					pst.setInt(2, player_id);
					pst.executeUpdate();
					p.sendMessage("You have successfully left team: "
							+ team_name);
					NS.sendPlayerUpdate(p.getName()
							+ " has left the arena team.",
							getTeamMembers(player_team_id), ChatColor.RED);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} finally {
					try {
						pst.close();
					} catch (SQLException e) {
					}
				}
			} else {
				p.sendMessage(ChatColor.RED
						+ "You are the owner of this team. Transfer ownership or disband team to leave.");
			}
		} else {
			p.sendMessage(ChatColor.RED + "You are not on an arena team.");
		}
	}

	public synchronized void setTeamOpenState(Player p) {
		int team_id = getPlayerTeamId(p);
		int player_id = getPlayerId(p);
		int openstate = getTeamOpenState(team_id);
		PreparedStatement pst = null;
		try {
			pst = conn
					.prepareStatement("UPDATE tables SET open='?' WHERE team_id='?' and owner='?';");
			if (openstate == 0) {
				pst.setInt(1, 1);
			} else {
				pst.setInt(1, 0);
			}
			pst.setInt(2, team_id);
			pst.setInt(3, player_id);
			pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				pst.close();
			} catch (SQLException e) {
			}
		}
	}

	// End Team Manager

	// TODO To Party Manager

	// Party Manager begins//
	private int getPartyLeader(int party_id) {
		return getIntValue("parties", "leader_id", "party_id", party_id, null);
	}

	private int getPartyId(int leader_id) {
		return getIntValue("parties", "party_id", "leader_id", leader_id, null);
	}

	private int getPlayerPartyId(int player_id) {
		return getIntValue("players", "party_id", "player_id", player_id, null);
	}

	private int getPartySize(int party_id) {
		return getIntValue("parties", "total_players", "party_id", party_id,
				null);
	}

	private int getPartyInvite(int player_id, int party_id) {
		return getDoubleCheckInt("party_invites", "invite_id", "player_id",
				"party_id", player_id, party_id);
	}

	private ResultSet getPartyMembers(int party_id) {
		return getMembers("players", "username", "party_id", party_id);
	}

	private void deletePartyInvite(Player joined, int party_id) {
		PreparedStatement pst = null;
		int player_id = getPlayerId(joined);
		try {
			pst = conn
					.prepareStatement("DELETE FROM party_invites WHERE player_id=? AND party_id=?");
			pst.setInt(1, player_id);
			pst.setInt(2, party_id);
			pst.executeUpdate();
		} catch (SQLException e) {
		} finally {
			try {
				pst.close();
			} catch (SQLException e) {
			}
		}
	}

	public synchronized void createParty(Player p) {
		PreparedStatement pst = null;
		int player_id = getPlayerId(p);
		int player_team_id = getPlayerTeamId(p);
		try {
			pst = conn
					.prepareStatement("INSERT INTO parties (team_id,total_players,leader_id) VALUES(?,?,?)");
			pst.setInt(1, player_team_id);
			pst.setInt(2, 1);
			pst.setInt(3, player_id);
			pst.executeUpdate();
			int party_id = getPartyId(player_id);
			pst = conn
					.prepareStatement("UPDATE players SET party_id=? WHERE player_id=?");
			pst.setInt(1, party_id);
			pst.setInt(2, player_id);
			pst.executeUpdate();
			p.sendMessage(ChatColor.GREEN
					+ "You have successfully created an arena party.");
		} catch (SQLException e) {
			if (e.getErrorCode() != 1062) {
				e.printStackTrace();
			} else {
				p.sendMessage(ChatColor.RED
						+ "You are already in an arena party.");
			}
		} finally {
			try {
				pst.close();
			} catch (SQLException e) {
			}
		}
	}

	public synchronized void disbandParty(Player p) {
		int player_id = getPlayerId(p);
		int party_id = getPlayerPartyId(player_id);
		int leader_id = getPartyLeader(party_id);
		if (player_id == leader_id) {
			PreparedStatement pst = null;
			try {
				NS.sendPlayerUpdate("Your arena party has been disbanded",
						getPartyMembers(party_id), ChatColor.RED);
				pst = conn
						.prepareStatement("DELETE FROM parties WHERE leader_id=?");
				pst.setInt(1, leader_id);
				pst.executeUpdate();
				pst = conn
						.prepareStatement("UPDATE players SET party_id=? WHERE party_id=?");
				pst.setNull(1, Type.INT);
				pst.setInt(2, party_id);
				pst.executeUpdate();
			} catch (SQLException e) {
			} finally {
				try {
					pst.close();
				} catch (SQLException e) {
				}
			}
		} else {
			p.sendMessage(ChatColor.RED
					+ "You are not the leader of this party.");
		}
	}

	public synchronized void partyInvite(Player p, String target) {
		Player invitee = Bukkit.getServer().getPlayer(target);
		int target_id = getPlayerId(invitee);
		int player_id = getPlayerId(p);
		int party_id = getPlayerPartyId(player_id);
		int leader_id = getPartyLeader(party_id);
		PreparedStatement pst = null;
		if (party_id != 0) {
			if (player_id == leader_id) {
				try {
					pst = conn
							.prepareStatement("INSERT INTO party_invites (player_id,party_id) VALUES(?,?)");
					pst.setInt(1, target_id);
					pst.setInt(2, party_id);
					pst.executeUpdate();
					invitee.sendMessage(ChatColor.RED
							+ "You have been invited to join " + p.getName()
							+ "'s arena party.");
					invitee.sendMessage("Type /arena party join <playername> to join the party.");
				} catch (SQLException e) {
					if (e.getErrorCode() != 1062) {
						e.printStackTrace();
					} else {
						p.sendMessage(ChatColor.RED
								+ "You already sent this person an invite.");
					}
				} finally {
					try {
						pst.close();
					} catch (SQLException e) {
					}
				}
			} else {
				p.sendMessage(ChatColor.RED
						+ "You are not the leader of this arena party.");
				p.sendMessage("Party_id:" + party_id);
				p.sendMessage("Leader_id:" + leader_id);
				p.sendMessage("Your_id:" + player_id);
			}
		} else {
			p.sendMessage(ChatColor.RED + "You are not in an arena party.");
		}
	}

	public synchronized void joinParty(Player p, String playername) {
		Player target = Bukkit.getServer().getPlayer(playername);
		int player_id = getPlayerId(p);
		int target_id = getPlayerId(target);
		int party_id = getPlayerPartyId(target_id);
		int party_size = getPartySize(party_id);
		int player_party_id = getPlayerPartyId(player_id);
		PreparedStatement pst = null;
		if (player_party_id == 0) {
			if (party_size != 2) {
				if (party_id != 0) {
					if (getPartyInvite(player_id, party_id) != 0) {
						try {
							pst = conn
									.prepareStatement("UPDATE players SET party_id=? WHERE player_id=?");
							pst.setInt(1, party_id);
							pst.setInt(2, player_id);
							pst.executeUpdate();
							target.sendMessage(pst.toString());
							pst = conn
									.prepareStatement("UPDATE parties SET total_players=? WHERE party_id=?");
							pst.setInt(1, party_size + 1);
							pst.setInt(2, party_id);
							pst.executeUpdate();
							p.sendMessage(ChatColor.GREEN
									+ "You have successfully joined "
									+ target.getName() + "'s arena party.");
							target.sendMessage(ChatColor.GREEN + p.getName()
									+ " has joined you arena party.");
							if (getPartyInvite(player_id, party_id) != 0) {
								deletePartyInvite(p, party_id);
							}
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						} finally {
							if (pst != null) {
								try {
									pst.close();
								} catch (SQLException ex) {

								}
							}
						}
					} else {
						p.sendMessage(ChatColor.RED
								+ "You do not have a pending invite.");
					}
				} else {
					p.sendMessage(ChatColor.RED
							+ "Unable to find the specified party.");
				}
			} else {
				p.sendMessage(ChatColor.RED + "This party is already full.");
				if (getPartyInvite(player_id, party_id) != 0) {
					deletePartyInvite(p, party_id);
				}
			}
		} else {
			p.sendMessage(ChatColor.RED + "You are already in a party.");
		}
	}

	public synchronized void kickPlayerFromParty(Player sender,
			String tobekicked) {
		Player target = Bukkit.getServer().getPlayer(tobekicked);
		int target_id = getPlayerId(target);
		int target_party = getPlayerPartyId(target_id);
		int sender_id = getPlayerId(sender);
		int party_id = getPlayerPartyId(sender_id);
		int leader_id = getPartyLeader(party_id);
		int party_size = getPartySize(party_id);
		PreparedStatement pst = null;
		if (party_id != 0) {
			if (target_party == party_id) {
				if (sender_id == leader_id) {
					try {
						pst = conn
								.prepareStatement("UPDATE players SET party_id=? WHERE player_id=?");
						pst.setNull(1, Type.INT);
						pst.setInt(1, target_id);
						pst.executeUpdate();
						pst = conn
								.prepareStatement("UPDATE parties SET total_players=? WHERE party_id=?");
						pst.setInt(1, party_size - 1);
						pst.setInt(2, party_id);
						pst.executeUpdate();
						sender.sendMessage(ChatColor.GREEN + "You have kicked "
								+ target.getName() + " from the arena party.");
						target.sendMessage(ChatColor.GREEN
								+ "You have been kicked from the arena party.");
					} catch (SQLException e) {
					} finally {
						try {
							pst.close();
						} catch (SQLException e) {
						}
					}

				} else {
					sender.sendMessage(ChatColor.RED
							+ "You are not the arena party leader.");
				}
			} else {
				sender.sendMessage(ChatColor.RED + target.getName()
						+ " is not in your arena party.");
			}
		} else {
			sender.sendMessage(ChatColor.RED + "You are not in an arena party.");
		}
	}

	public synchronized void leaveParty(Player sender) {
		int player_id = getPlayerId(sender);
		int party_id = getPlayerPartyId(player_id);
		int leader_id = getPartyLeader(party_id);
		int party_size = getPartySize(party_id);
		PreparedStatement pst = null;
		if (party_id != 0) {
			if (player_id != leader_id) {
				try {
					pst = conn
							.prepareStatement("UPDATE parties SET total_players=? WHERE party_id=?");
					pst.setInt(1, party_size - 1);
					pst.setInt(2, party_id);
					pst.executeUpdate();
					pst = conn
							.prepareStatement("UPDATE players SET party_id=? WHERE player_id=?");
					pst.setNull(1, Type.INT);
					pst.setInt(2, player_id);
					pst.executeUpdate();
					sender.sendMessage(ChatColor.GREEN
							+ "You have successfully left the arena party.");
					NS.sendPlayerUpdate(sender.getName()
							+ " has left the arena party.",
							getPartyMembers(party_id), ChatColor.RED);
				} catch (SQLException e) {
				} finally {
					try {
						pst.close();
					} catch (SQLException e) {
					}
				}
			} else {
				sender.sendMessage(ChatColor.RED
						+ "You are the leader of this arena party.");
				sender.sendMessage(ChatColor.RED
						+ "To leave pass ownership or disband party.");
			}
		} else {
			sender.sendMessage(ChatColor.RED + "You are not in an arena party.");
		}
	}
	// Party Manager Ends//
}
